package com.hello;

import com.ianywhere.ultralitejni12.Connection;
import com.ianywhere.ultralitejni12.PreparedStatement;
import com.ianywhere.ultralitejni12.ULjException;

public class SchemaCreation {
	Connection conn;
	
	public SchemaCreation(Connection conn) {
		this.conn = conn;
	}
	
	public void create() throws ULjException {
		createCustomer();
		createContact();
		//createProducts();
		//createCustomerProduct();
		//createSalesOrder();
		createMetaData();
	}

	private void createContact() throws ULjException {
		 String stmt = "CREATE TABLE IF NOT EXISTS Contacts    ( "+
						"ID integer not null, "+
						"Surname char(20) not null, "+
						"GivenName char(20) not null, "+
						"Title varchar(34) null, "+
						"Street char(30) null, "+
						"City char(20) null, "+
						"State char(16) null, "+
						"Country char(16) null, "+
						"PostalCode char(10) null, "+
						"Phone char(13) null, "+
						"Fax char(13) null, "+
						"CustomerID integer null, "+
						"PRIMARY KEY (ID) )";
	      executeStmt(stmt); 
	      stmt = "ALTER TABLE Contacts ADD FOREIGN KEY FK_CustomerID_ID (CustomerID	) REFERENCES Customers (ID)";
    executeStmt(stmt);
    
		
	}

	private void createMetaData() throws ULjException {
        String stmt = "CREATE TABLE MetaData ( id INTEGER not null default autoincrement" + 
                ", host VARCHAR(50), port INTEGER, PRIMARY KEY ( id ), synchronize off )";
        executeStmt(stmt);
		
	}

	private void createSalesOrder() throws ULjException {
		String stmt = "CREATE TABLE SalesOrders    ( "+
						"ID integer not null default autoincrement, "+
						"CustomerID integer not null, "+
						"OrderDate date not null, "+
						"FinancialCode char(2) null, "+
						"Region char(7) null, "+
						"SalesRepresentative integer not null, "+
						"PRIMARY KEY (ID) "+
				")";
		executeStmt(stmt);	
		stmt = "ALTER TABLE SalesOrders ADD FOREIGN KEY FK_CustomerID_ID (CustomerID)REFERENCES Customers (ID)";
		executeStmt(stmt);
	}

	private void createCustomerProduct() throws ULjException {
		String stmt = "CREATE TABLE CustomerProducts    ( "+
						"ID integer not null default autoincrement, "+
						"SalesOrderID integer not null, "+
						"CustomerID integer not null, "+
						"ProductID integer null, "+
						"PRIMARY KEY (ID) "+
						")";
			executeStmt(stmt);	
	}

	private void createProducts() throws ULjException {
		String stmt = "CREATE TABLE Products   ( "+
						"ID integer not null, "+
						"Name char(15) not null, "+
						"Description char(30) not null, "+
						"Size char(18) not null, "+
						"Color char(18) not null, "+
						"Quantity integer not null, "+
						"UnitPrice numeric(15, 2) not null, "+
						"Photo long binary null, "+
						"PRIMARY KEY (ID) "+
					")";
		executeStmt(stmt);		
	}

	private void createCustomer() throws ULjException {
		String stmt = "CREATE TABLE Customers    ( "+
				"ID integer not null default autoincrement, "+ 
				"Surname char(20) not null, "+
				"GivenName char(20) not null, "+
				"Street char(30) not null, "+
				"City char(20) not null, "+
				"State char(16) null, "+
				"Country char(16) null, "+
				"PostalCode char(10) null, "+
				"Phone char(13) not null, "+
				"CompanyName char(32) null, "+ 
				"PRIMARY KEY (ID) "+
			")";		/*
		String stmt = "CREATE TABLE Customers  (" +
				"ID integer , " +
				"Surname varchar(20), " +
				"GivenName varchar(20),  " +
				"Street varchar(30),  " +
				"City varchar(20) , " +
				"State varchar(16) , " +
				"Country varchar(16) , " +
				"PostalCode varchar(10) , " +
				"Phone varchar(13) not , " +
				"CompanyName varchar(32) , " +
				"Region varchar(255) , " +
				"PRIMARY KEY (ID) " +
				")";
		*/
		executeStmt(stmt);
	}

	//wykonanie statementu
	void executeStmt(String stmt) throws ULjException {
        PreparedStatement ps = conn.prepareStatement(stmt);
        ps.execute();
        conn.commit();
        ps.close();
	}

}
